I was recently working on a data mart project, and was looking to create a UAT version of the mart. Whilst the mart itself would be subject to constant change and rebuilding as the development progresses, the UAT database will be refreshed only at agreed intervals, and be a stable version for end user review and testing. What I wanted though was not to have to maintain a second ETL process to populate the UAT database.
My solution was to use one of my favourite undocumented stored procedures; sp_MSForEachTable.
This procedure allows the user to define a SQL statement which will be executed against each table in a database, with a question mark '?' being used as a placeholder for the table name. For example; the following statement will drop every table in a database, in this case the UAT database:
Use UAT_Database
go
exec sp_MSForEachTable 'Drop table ?'
go
Now that I have cleared out the UAT database I need to populate it afresh with the contents of the mart. The following script will blindly copy all tabled from the mart to the UAT database:
exec Mart_Database.dbo.sp_MSForEachTable 'Select * into UAT_Database.? from ?'
go